import sqlite3
import os
from typing import Optional, List
from dataclasses import dataclass
from datetime import datetime


@dataclass
class VideoInfo:
    """视频信息数据类"""
    aweme_id: str
    platform: str
    title: str
    description: str
    duration: int
    cover_url: str
    video_url: str
    author_name: str
    author_id: str
    play_count: int
    like_count: int
    comment_count: int
    download_path: Optional[str] = None
    download_time: Optional[datetime] = None
    search_keyword: Optional[str] = None
    create_time: Optional[datetime] = None
    # 视频处理状态相关字段
    processing_status: Optional[str] = None  # None, pending, completed, failed
    ghostcut_task_id: Optional[str] = None
    processed_video_url: Optional[str] = None
    processed_download_path: Optional[str] = None
    last_check_time: Optional[datetime] = None
    retry_count: int = 0  # 重试次数，默认为0


@dataclass
class KeywordConfig:
    """关键词配置数据类"""
    id: Optional[int] = None
    keyword: str = ""
    count: int = 0
    enabled: bool = True
    last_run_time: Optional[str] = ""
    create_time: Optional[str] = ""
    platform: str = "douyin"  # 添加平台字段，默认为douyin
    source_lang: str = "zh"   # 原语言，默认为中文
    target_lang: str = "vi"   # 目标语言，默认为越南语


class VideoDatabase:
    """视频数据库管理工具"""
    
    def __init__(self, db_path: str = None):
        """初始化数据库"""
        if db_path is None:
            # 使用项目根目录下的data文件夹
            project_root = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
            data_dir = os.path.join(project_root, 'data')
            if not os.path.exists(data_dir):
                os.makedirs(data_dir)
            self.db_path = os.path.join(data_dir, 'video_database.db')
        else:
            self.db_path = db_path
        
        # 初始化数据库表
        self._init_database()
    
    def _init_database(self):
        """初始化数据库表"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 检查是否已存在videos表
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='videos'")
            table_exists = cursor.fetchone()
            
            if not table_exists:
                # 创建视频信息表
                cursor.execute("""
                    CREATE TABLE videos (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        aweme_id TEXT UNIQUE NOT NULL,
                        platform TEXT NOT NULL,
                        title TEXT,
                        description TEXT,
                        duration INTEGER,
                        cover_url TEXT,
                        video_url TEXT,
                        author_name TEXT,
                        author_id TEXT,
                        play_count INTEGER,
                        like_count INTEGER,
                        comment_count INTEGER,
                        download_path TEXT,
                        download_time TIMESTAMP,
                        search_keyword TEXT,
                        create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        processing_status TEXT,
                        ghostcut_task_id TEXT,
                        processed_video_url TEXT,
                        processed_download_path TEXT,
                        last_check_time TIMESTAMP,
                        retry_count INTEGER DEFAULT 0
                    )
                """)
            else:
                # 检查是否存在aweme_id列（用于从旧版本迁移）
                cursor.execute("PRAGMA table_info(videos)")
                columns = cursor.fetchall()
                column_names = [column[1] for column in columns]
                
                # 如果存在video_id列但不存在aweme_id列，则重命名列
                if "video_id" in column_names and "aweme_id" not in column_names:
                    # SQLite不支持直接重命名列，需要重建表
                    cursor.execute("""
                        CREATE TABLE videos_new (
                            id INTEGER PRIMARY KEY AUTOINCREMENT,
                            aweme_id TEXT UNIQUE NOT NULL,
                            platform TEXT NOT NULL,
                            title TEXT,
                            description TEXT,
                            duration INTEGER,
                            cover_url TEXT,
                            video_url TEXT,
                            author_name TEXT,
                            author_id TEXT,
                            play_count INTEGER,
                            like_count INTEGER,
                            comment_count INTEGER,
                            download_path TEXT,
                            download_time TIMESTAMP,
                            search_keyword TEXT,
                            create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                            processing_status TEXT,
                            ghostcut_task_id TEXT,
                            processed_video_url TEXT,
                            processed_download_path TEXT,
                            last_check_time TIMESTAMP,
                            retry_count INTEGER DEFAULT 0
                        )
                    """)
                    
                    # 复制数据
                    columns_to_copy = [col for col in column_names if col != "video_id"]
                    columns_str = ", ".join(columns_to_copy)
                    cursor.execute(f"""
                        INSERT INTO videos_new (aweme_id, {columns_str})
                        SELECT video_id, {columns_str} FROM videos
                    """)
                    
                    # 删除旧表并重命名新表
                    cursor.execute("DROP TABLE videos")
                    cursor.execute("ALTER TABLE videos_new RENAME TO videos")
                
                # 检查是否已存在retry_count列，如果不存在则添加
                if "retry_count" not in column_names:
                    cursor.execute("ALTER TABLE videos ADD COLUMN retry_count INTEGER DEFAULT 0")
            
            # 创建关键词配置表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS keyword_configs (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    keyword TEXT NOT NULL,
                    count INTEGER NOT NULL DEFAULT 5,
                    enabled BOOLEAN NOT NULL DEFAULT 1,
                    last_run_time TIMESTAMP,
                    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    platform TEXT NOT NULL DEFAULT 'douyin',
                    source_lang TEXT NOT NULL DEFAULT 'zh',
                    target_lang TEXT NOT NULL DEFAULT 'vi'
                )
            """)
            
            # 创建唯一索引，基于keyword, source_lang, target_lang
            cursor.execute("""
                CREATE UNIQUE INDEX IF NOT EXISTS idx_keyword_langs 
                ON keyword_configs(keyword, source_lang, target_lang)
            """)
            
            # 检查keyword_configs表是否存在platform列，如果不存在则添加
            cursor.execute("PRAGMA table_info(keyword_configs)")
            columns = cursor.fetchall()
            column_names = [column[1] for column in columns]
            
            # 如果不存在platform列，则添加该列
            if "platform" not in column_names:
                cursor.execute("ALTER TABLE keyword_configs ADD COLUMN platform TEXT NOT NULL DEFAULT 'douyin'")
            
            # 如果不存在source_lang列，则添加该列
            if "source_lang" not in column_names:
                    cursor.execute("ALTER TABLE keyword_configs ADD COLUMN source_lang TEXT NOT NULL DEFAULT 'zh'")
                
            # 如果不存在target_lang列，则添加该列
            if "target_lang" not in column_names:
                cursor.execute("ALTER TABLE keyword_configs ADD COLUMN target_lang TEXT NOT NULL DEFAULT 'vi'")
        
            # 创建索引以提高查询性能
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_aweme_id ON videos(aweme_id)")
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_platform ON videos(platform)")
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_author_id ON videos(author_id)")
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_search_keyword ON videos(search_keyword)")
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_processing_status ON videos(processing_status)")
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_keyword_enabled ON keyword_configs(enabled)")
            
            # 创建Sora2视频表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS sora2_videos (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    task_id TEXT UNIQUE NOT NULL,
                    prompt TEXT,
                    orientation TEXT,
                    video_path TEXT,
                    download_path TEXT,
                    status TEXT,
                    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    processed_time TIMESTAMP
                )
            """)
            
            conn.commit()
    
    def add_video(self, video_info: VideoInfo) -> bool:
        """
        添加视频信息到数据库
        
        Args:
            video_info (VideoInfo): 视频信息对象
            
        Returns:
            bool: 添加成功返回True，如果视频已存在则返回False
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("""
                    INSERT OR IGNORE INTO videos (
                        aweme_id, platform, title, description, duration,
                        cover_url, video_url, author_name, author_id,
                        play_count, like_count, comment_count, search_keyword
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """, (
                    video_info.aweme_id, video_info.platform, video_info.title,
                    video_info.description, video_info.duration, video_info.cover_url,
                    video_info.video_url, video_info.author_name, video_info.author_id,
                    video_info.play_count, video_info.like_count, video_info.comment_count,
                    video_info.search_keyword
                ))
                conn.commit()
                return cursor.rowcount > 0
        except Exception as e:
            print(f"添加视频信息到数据库时出错: {e}")
            return False
    
    def get_video_by_id(self, aweme_id: str) -> Optional[VideoInfo]:
        """
        根据aweme_id获取视频信息
        
        Args:
            aweme_id (str): 抖音视频ID
            
        Returns:
            VideoInfo: 视频信息对象，如果不存在则返回None
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT * FROM videos WHERE aweme_id = ?", (aweme_id,))
                row = cursor.fetchone()
                if row:
                    return VideoInfo(
                        aweme_id=row[1], platform=row[2], title=row[3],
                        description=row[4], duration=row[5], cover_url=row[6],
                        video_url=row[7], author_name=row[8], author_id=row[9],
                        play_count=row[10], like_count=row[11], comment_count=row[12],
                        download_path=row[13], download_time=row[14], search_keyword=row[15],
                        create_time=row[16], processing_status=row[17], ghostcut_task_id=row[18],
                        processed_video_url=row[19], processed_download_path=row[20],
                        last_check_time=row[21], retry_count=row[22] or 0
                    )
                return None
        except Exception as e:
            print(f"查询视频信息时出错: {e}")
            return None
    
    def update_download_info(self, aweme_id: str, download_path: str) -> bool:
        """
        更新视频下载信息
        
        Args:
            aweme_id (str): 抖音视频ID
            download_path (str): 下载路径
            
        Returns:
            bool: 更新成功返回True
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("""
                    UPDATE videos 
                    SET download_path = ?, download_time = CURRENT_TIMESTAMP 
                    WHERE aweme_id = ?
                """, (download_path, aweme_id))
                conn.commit()
                return cursor.rowcount > 0
        except Exception as e:
            print(f"更新视频下载信息时出错: {e}")
            return False
    
    def update_processing_status(self, aweme_id: str, status: str, 
                               task_id: str = None, processed_url: str = None, 
                               retry_count: int = None) -> bool:
        """
        更新视频处理状态
        
        Args:
            aweme_id (str): 抖音视频ID
            status (str): 处理状态 (initialized, pending, completed, failed)
            task_id (str, optional): 鬼手任务ID
            processed_url (str, optional): 处理后的视频URL
            retry_count (int, optional): 重试次数
            
        Returns:
            bool: 更新成功返回True
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                if retry_count is not None:
                    cursor.execute("""
                        UPDATE videos 
                        SET processing_status = ?, ghostcut_task_id = ?, 
                            processed_video_url = ?, last_check_time = CURRENT_TIMESTAMP,
                            retry_count = ?
                        WHERE aweme_id = ?
                    """, (status, task_id, processed_url, retry_count, aweme_id))
                else:
                    cursor.execute("""
                        UPDATE videos 
                        SET processing_status = ?, ghostcut_task_id = ?, 
                            processed_video_url = ?, last_check_time = CURRENT_TIMESTAMP
                        WHERE aweme_id = ?
                    """, (status, task_id, processed_url, aweme_id))
                conn.commit()
                return cursor.rowcount > 0
        except Exception as e:
            print(f"更新视频处理状态时出错: {e}")
            return False
    
    def get_videos_by_keyword(self, keyword: str) -> List[VideoInfo]:
        """
        根据搜索关键词获取视频列表
        
        Args:
            keyword (str): 搜索关键词
            
        Returns:
            List[VideoInfo]: 视频信息列表
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT * FROM videos WHERE search_keyword = ?", (keyword,))
                rows = cursor.fetchall()
                return [VideoInfo(
                    aweme_id=row[1], platform=row[2], title=row[3],
                    description=row[4], duration=row[5], cover_url=row[6],
                    video_url=row[7], author_name=row[8], author_id=row[9],
                    play_count=row[10], like_count=row[11], comment_count=row[12],
                    download_path=row[13], download_time=row[14], search_keyword=row[15],
                    create_time=row[16], processing_status=row[17], ghostcut_task_id=row[18],
                    processed_video_url=row[19], processed_download_path=row[20],
                    last_check_time=row[21], retry_count=row[22] or 0
                ) for row in rows]
        except Exception as e:
            print(f"查询关键词相关视频时出错: {e}")
            return []
    
    def video_exists(self, aweme_id: str) -> bool:
        """
        检查视频是否已存在于数据库中
        
        Args:
            aweme_id (str): 抖音视频ID
            
        Returns:
            bool: 存在返回True，否则返回False
        """
        return self.get_video_by_id(aweme_id) is not None
    
    def get_pending_videos(self) -> List[VideoInfo]:
        """
        获取所有处理状态为pending的视频
        
        Returns:
            List[VideoInfo]: 处理中的视频列表
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT * FROM videos WHERE processing_status = 'pending'")
                rows = cursor.fetchall()
                return [VideoInfo(
                    aweme_id=row[1], platform=row[2], title=row[3],
                    description=row[4], duration=row[5], cover_url=row[6],
                    video_url=row[7], author_name=row[8], author_id=row[9],
                    play_count=row[10], like_count=row[11], comment_count=row[12],
                    download_path=row[13], download_time=row[14], search_keyword=row[15],
                    create_time=row[16], processing_status=row[17], ghostcut_task_id=row[18],
                    processed_video_url=row[19], processed_download_path=row[20],
                    last_check_time=row[21], retry_count=row[22] or 0
                ) for row in rows]
        except Exception as e:
            print(f"查询处理中视频时出错: {e}")
            return []
    
    def mark_expired_videos_as_timeout(self) -> int:
        """
        将超过24小时的pending状态视频标记为timeout
        
        Returns:
            int: 更新的视频数量
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                # 更新超过24小时且状态为pending的视频状态为timeout
                cursor.execute("""
                    UPDATE videos 
                    SET processing_status = 'timeout'
                    WHERE processing_status = 'pending' 
                    AND datetime(last_check_time) <= datetime('now', '-1 day')
                """)
                conn.commit()
                return cursor.rowcount
        except Exception as e:
            print(f"更新超时视频状态时出错: {e}")
            return 0
    
    def update_processed_download_info(self, aweme_id: str, download_path: str) -> bool:
        """
        更新处理后视频的下载信息
        
        Args:
            aweme_id (str): 抖音视频ID
            download_path (str): 下载路径
            
        Returns:
            bool: 更新成功返回True
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("""
                    UPDATE videos 
                    SET processed_download_path = ?, download_time = CURRENT_TIMESTAMP 
                    WHERE aweme_id = ?
                """, (download_path, aweme_id))
                conn.commit()
                return cursor.rowcount > 0
        except Exception as e:
            print(f"更新处理后视频下载信息时出错: {e}")
            return False
    
    def get_all_downloaded_videos(self) -> List[VideoInfo]:
        """
        获取所有已下载的视频
        
        Returns:
            List[VideoInfo]: 已下载的视频列表
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT * FROM videos WHERE download_path IS NOT NULL")
                rows = cursor.fetchall()
                return [VideoInfo(
                    aweme_id=row[1], platform=row[2], title=row[3],
                    description=row[4], duration=row[5], cover_url=row[6],
                    video_url=row[7], author_name=row[8], author_id=row[9],
                    play_count=row[10], like_count=row[11], comment_count=row[12],
                    download_path=row[13], download_time=row[14], search_keyword=row[15],
                    create_time=row[16], processing_status=row[17], ghostcut_task_id=row[18],
                    processed_video_url=row[19], processed_download_path=row[20],
                    last_check_time=row[21], retry_count=row[22] or 0
                ) for row in rows]
        except Exception as e:
            print(f"查询已下载视频时出错: {e}")
            return []
    
    # 关键词配置相关方法
    
    def add_keyword_config(self, keyword: str, count: int = 5, enabled: bool = True, platform: str = "douyin", 
                          source_lang: str = "zh", target_lang: str = "vi") -> bool:
        """
        添加关键词配置
        
        Args:
            keyword (str): 关键词
            count (int): 收集数量
            enabled (bool): 是否启用
            platform (str): 平台
            source_lang (str): 原语言 (en, zh, th, vi)
            target_lang (str): 目标语言 (vi, th)
            
        Returns:
            bool: 添加成功返回True
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                # 检查是否已存在相同的keyword, source_lang, target_lang组合
                cursor.execute("""
                    SELECT id FROM keyword_configs 
                    WHERE keyword = ? AND source_lang = ? AND target_lang = ?
                """, (keyword, source_lang, target_lang))
                
                existing = cursor.fetchone()
                if existing:
                    # 如果已存在，更新现有记录而不是插入新记录
                    cursor.execute("""
                        UPDATE keyword_configs 
                        SET count = ?, enabled = ?, platform = ?
                        WHERE id = ?
                    """, (count, enabled, platform, existing[0]))
                else:
                    # 如果不存在，插入新记录
                    cursor.execute("""
                        INSERT INTO keyword_configs (keyword, count, enabled, platform, source_lang, target_lang)
                        VALUES (?, ?, ?, ?, ?, ?)
                    """, (keyword, count, enabled, platform, source_lang, target_lang))
                
                conn.commit()
                return True
        except Exception as e:
            print(f"添加关键词配置时出错: {e}")
            return False
    
    def get_keyword_configs(self, enabled_only: bool = True) -> List[KeywordConfig]:
        """
        获取关键词配置列表
        
        Args:
            enabled_only (bool): 是否只获取启用的配置
            
        Returns:
            List[KeywordConfig]: 关键词配置列表
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                if enabled_only:
                    cursor.execute("""
                        SELECT id, keyword, count, enabled, last_run_time, create_time, platform, source_lang, target_lang
                        FROM keyword_configs 
                        WHERE enabled = 1
                        ORDER BY create_time DESC
                    """)
                else:
                    cursor.execute("""
                        SELECT id, keyword, count, enabled, last_run_time, create_time, platform, source_lang, target_lang
                        FROM keyword_configs 
                        ORDER BY create_time DESC
                    """)
                
                rows = cursor.fetchall()
                return [KeywordConfig(
                    id=row[0], 
                    keyword=row[1], 
                    count=row[2], 
                    enabled=bool(row[3]), 
                    last_run_time=row[4], 
                    create_time=row[5],
                    platform=row[6],
                    source_lang=row[7],
                    target_lang=row[8]
                ) for row in rows]
        except Exception as e:
            print(f"获取关键词配置时出错: {e}")
            return []
    
    def update_keyword_config(self, keyword_id: int, keyword: str = None, count: int = None, 
                              enabled: bool = None, platform: str = None, 
                              source_lang: str = None, target_lang: str = None) -> bool:
        """
        更新关键词配置
        
        Args:
            keyword_id (int): 关键词配置ID
            keyword (str, optional): 关键词
            count (int, optional): 收集数量
            enabled (bool, optional): 是否启用
            platform (str, optional): 平台
            source_lang (str, optional): 原语言 (en, zh, th, vi)
            target_lang (str, optional): 目标语言 (vi, th)
            
        Returns:
            bool: 更新成功返回True
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 获取当前记录的信息
                cursor.execute("SELECT keyword, source_lang, target_lang FROM keyword_configs WHERE id = ?", (keyword_id,))
                current_record = cursor.fetchone()
                
                if not current_record:
                    return False  # 记录不存在
                
                current_keyword, current_source_lang, current_target_lang = current_record
                
                # 如果要更新keyword, source_lang或target_lang，需要检查是否会与现有记录冲突
                new_keyword = keyword if keyword is not None else current_keyword
                new_source_lang = source_lang if source_lang is not None else current_source_lang
                new_target_lang = target_lang if target_lang is not None else current_target_lang
                
                # 检查新的组合是否已存在（排除当前记录）
                cursor.execute("""
                    SELECT id FROM keyword_configs 
                    WHERE keyword = ? AND source_lang = ? AND target_lang = ? AND id != ?
                """, (new_keyword, new_source_lang, new_target_lang, keyword_id))
                
                existing = cursor.fetchone()
                if existing:
                    raise ValueError(f"关键词配置已存在: keyword={new_keyword}, source_lang={new_source_lang}, target_lang={new_target_lang}")
                
                # 构建动态更新语句
                updates = []
                params = []
                
                if keyword is not None:
                    updates.append("keyword = ?")
                    params.append(keyword)
                
                if count is not None:
                    updates.append("count = ?")
                    params.append(count)
                
                if enabled is not None:
                    updates.append("enabled = ?")
                    params.append(enabled)
                
                if platform is not None:
                    updates.append("platform = ?")
                    params.append(platform)
                
                if source_lang is not None:
                    updates.append("source_lang = ?")
                    params.append(source_lang)
                
                if target_lang is not None:
                    updates.append("target_lang = ?")
                    params.append(target_lang)
                
                if not updates:
                    return True  # 没有需要更新的字段
                
                # 添加ID到参数列表
                params.append(keyword_id)
                
                # 执行更新
                query = f"UPDATE keyword_configs SET {', '.join(updates)} WHERE id = ?"
                cursor.execute(query, params)
                conn.commit()
                return cursor.rowcount > 0
        except ValueError as e:
            print(f"更新关键词配置时出错: {e}")
            raise
        except Exception as e:
            print(f"更新关键词配置时出错: {e}")
            return False
    
    def delete_keyword_config(self, keyword_id: int) -> bool:
        """
        删除关键词配置
        
        Args:
            keyword_id (int): 关键词配置ID
            
        Returns:
            bool: 删除成功返回True
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("DELETE FROM keyword_configs WHERE id = ?", (keyword_id,))
                conn.commit()
                return cursor.rowcount > 0
        except Exception as e:
            print(f"删除关键词配置时出错: {e}")
            return False
    
    def update_keyword_last_run_time(self, keyword: str) -> bool:
        """
        更新关键词最后运行时间
        
        Args:
            keyword (str): 关键词
            
        Returns:
            bool: 更新成功返回True
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("""
                    UPDATE keyword_configs 
                    SET last_run_time = CURRENT_TIMESTAMP 
                    WHERE keyword = ?
                """, (keyword,))
                conn.commit()
                return cursor.rowcount > 0
        except Exception as e:
            print(f"更新关键词最后运行时间时出错: {e}")
            return False

    def get_keyword_config_by_keyword(self, keyword: str) -> Optional[KeywordConfig]:
        """
        根据关键词获取配置信息
        
        Args:
            keyword (str): 关键词
            
        Returns:
            KeywordConfig: 关键词配置信息，如果不存在则返回None
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("""
                    SELECT id, keyword, count, enabled, last_run_time, create_time, platform, source_lang, target_lang
                    FROM keyword_configs 
                    WHERE keyword = ?
                    ORDER BY create_time DESC
                    LIMIT 1
                """, (keyword,))
                
                row = cursor.fetchone()
                if row:
                    return KeywordConfig(
                        id=row[0], 
                        keyword=row[1], 
                        count=row[2], 
                        enabled=bool(row[3]), 
                        last_run_time=row[4], 
                        create_time=row[5],
                        platform=row[6],
                        source_lang=row[7],
                        target_lang=row[8]
                    )
                return None
        except Exception as e:
            print(f"获取关键词配置时出错: {e}")
            return None


def demo():
    """演示数据库工具的使用"""
    # 创建数据库实例
    db = VideoDatabase()
    
    # 添加关键词配置
    db.add_keyword_config("科技", 10, True)
    db.add_keyword_config("娱乐", 15, True)
    db.add_keyword_config("教育", 8, False)
    
    # 获取启用的关键词配置
    keywords = db.get_keyword_configs(enabled_only=True)
    print(f"启用的关键词配置: {[k.keyword for k in keywords]}")
    
    # 更新关键词配置
    db.update_keyword_config("科技", count=20)
    
    # 更新关键词最后运行时间
    db.update_keyword_last_run_time("科技")
    
    # 创建示例视频信息
    video = VideoInfo(
        aweme_id="123456789",
        platform="tiktok",
        title="示例视频标题",
        description="这是一个示例视频描述",
        duration=60,
        cover_url="https://example.com/cover.jpg",
        video_url="https://example.com/video.mp4",
        author_name="示例作者",
        author_id="user_123",
        play_count=1000,
        like_count=50,
        comment_count=10,
        search_keyword="科技"
    )
    
    # 添加视频信息
    if db.add_video(video):
        print("视频信息添加成功")
    else:
        print("视频信息已存在或添加失败")
    
    # 查询视频信息
    retrieved_video = db.get_video_by_id("123456789")
    if retrieved_video:
        print(f"查询到视频: {retrieved_video.title}")
    
    # 更新下载信息
    db.update_download_info("123456789", "/path/to/downloaded/video.mp4")
    
    # 检查视频是否存在
    if db.video_exists("123456789"):
        print("视频已存在于数据库中")
    
    # 根据关键词查询视频
    videos = db.get_videos_by_keyword("科技")
    print(f"找到 {len(videos)} 个相关视频")
    
    # 获取所有已下载视频
    downloaded_videos = db.get_all_downloaded_videos()
    print(f"共有 {len(downloaded_videos)} 个已下载视频")


if __name__ == "__main__":
    demo()